package model.dba;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import model.PersonalAdministrativo;
import model.Tipo_Empleado;

public class PersonalAdministrativoDAO extends JDBC {
	private static PersonalAdministrativoDAO personalAdministrativoDAO;

	private PersonalAdministrativoDAO() {
		super();
	}

	public static PersonalAdministrativoDAO getInstance() {
		if (personalAdministrativoDAO == null) {
			personalAdministrativoDAO = new PersonalAdministrativoDAO();
		}
		return personalAdministrativoDAO;
	}

	public Integer add(PersonalAdministrativo personalAdministrativo) {

		try {
			String queryString = "INSERT INTO personalAdmin(dni, nombre, apellido, direccion, tel, cel, fechaNac, tipo, idusuario)" + " VALUES(?, ?, ?, ?, ?, ?,?,?, ?) ";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, personalAdministrativo.getDNI());
			preparedStatement.setString(2, personalAdministrativo.getNombre());
			preparedStatement.setString(3, personalAdministrativo.getApellido());
			preparedStatement.setString(4, personalAdministrativo.getEmail());
			preparedStatement.setString(5, personalAdministrativo.getTelefono());
			preparedStatement.setString(6, personalAdministrativo.getCelular());
			preparedStatement.setTimestamp(7, new Timestamp(personalAdministrativo.getNacimiento().getTimeInMillis()));
			preparedStatement.setString(8, personalAdministrativo.getTipo().toString());
			preparedStatement.setLong(9, personalAdministrativo.getIdUsuario());

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(PersonalAdministrativo personalAdministrativo) {
		boolean state = false;

		try {
			String queryString = "UPDATE personalAdmin SET  nombre=?, apellido=?,  direccion=?, tel=?, cel=?, fechaNac=? WHERE dni=?";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setString(1, personalAdministrativo.getNombre());
			preparedStatement.setString(2, personalAdministrativo.getApellido());
			preparedStatement.setString(3, personalAdministrativo.getEmail());
			preparedStatement.setString(4, personalAdministrativo.getTelefono());
			preparedStatement.setString(5, personalAdministrativo.getCelular());
			preparedStatement.setTimestamp(6, new Timestamp(personalAdministrativo.getNacimiento().getTimeInMillis()));
//			preparedStatement.setString(7, personalAdministrativo.getTipo().toString());
//			preparedStatement.setLong(8,personalAdministrativo.getIdUsuario());// usuario
			preparedStatement.setString(7, personalAdministrativo.getDNI());// cambie por el 9
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(PersonalAdministrativo personalAdministrativo) {
		boolean state = false;

		try {
			String queryString = "DELETE FROM personalAdmin WHERE dni=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, personalAdministrativo.getDNI());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public PersonalAdministrativo get(String DNI) {

		PersonalAdministrativo personalAdministrativo = new PersonalAdministrativo();
		try {
			String queryString = "SELECT * FROM personalAdmin where dni = '" + DNI + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				personalAdministrativo = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return personalAdministrativo;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT dni FROM personalAdmin order by dni DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("dni");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<PersonalAdministrativo> getAll() {
		List<PersonalAdministrativo> personalAdministrativoList = new ArrayList<PersonalAdministrativo>();

		try {
			String queryString = "SELECT * FROM personalAdmin;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				personalAdministrativoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return personalAdministrativoList;
	}

	public List<PersonalAdministrativo> get(Tipo_Empleado tipo) {
		List<PersonalAdministrativo> personalAdministrativoList = new ArrayList<PersonalAdministrativo>();

		try {
			String queryString = "SELECT * FROM personalAdmin where tipo = '" + tipo.toString() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				personalAdministrativoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return personalAdministrativoList;
	}

	public List<PersonalAdministrativo> find(String nombre, String apellido) {
		List<PersonalAdministrativo> personalAdministrativoList = new ArrayList<PersonalAdministrativo>();

		try {
			String queryString = "SELECT * FROM personalAdmin where nombre = '" + nombre + "' and apellido= '" + apellido + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				personalAdministrativoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return personalAdministrativoList;
	}
	
	public PersonalAdministrativo find(int idusuario) {

		PersonalAdministrativo personal = new PersonalAdministrativo();
		try {
			String queryString = "SELECT * FROM personaladmin where idusuario = '" + idusuario + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				personal = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return personal;
	}

	private PersonalAdministrativo populate() throws SQLException {
		// OBSERVAR SI TENDRA UN USUAIRO
		PersonalAdministrativo personalAdministrativo = new PersonalAdministrativo();
		personalAdministrativo.setApellido(resultSet.getString("apellido"));
		personalAdministrativo.setCelular(resultSet.getString("cel"));
		personalAdministrativo.setDNI(resultSet.getString("dni"));
		personalAdministrativo.setEmail(resultSet.getString("direccion"));
		GregorianCalendar nacimiento = new GregorianCalendar();
		nacimiento.setTime(resultSet.getTimestamp("fechaNac"));
		personalAdministrativo.setNacimiento(nacimiento);
		personalAdministrativo.setNombre(resultSet.getString("nombre"));
		personalAdministrativo.setTelefono(resultSet.getString("tel"));
		personalAdministrativo.setTipo(Tipo_Empleado.PersonalAdministrativo);
		personalAdministrativo.setEliminado(resultSet.getString("eliminado"));
		personalAdministrativo.setIdUsuario((int)resultSet.getLong("idusuario"));
		// personalAdministrativo.setTipo(Tipo_Empleado.valueOf(resultSet.getString("tipo")));

		return personalAdministrativo;

	}

	public boolean updateBaja(PersonalAdministrativo personalAdministrativo) {
		boolean state = false;

		try {
			String queryString = "UPDATE personalAdmin SET  eliminado=? WHERE dni=?";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setString(1, personalAdministrativo.getEliminado());
			preparedStatement.setString(2, personalAdministrativo.getDNI());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

}